/*
 * DataBaseReader.java
 *
 * Copyright (c) 2008 Teamnet. All Rights Reserved.
 *
 * This source file may not be copied, modified or redistributed,
 * in whole or in part, in any form or for any reason, without the express
 * written consent of Teamnet.
 */
package ro.prjmanager.database;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import ro.prjmanager.bundle.BundleClReader;
import ro.prjmanager.bundle.util.Utils;
import ro.prjmanager.database.conversion.Type;
import ro.prjmanager.database.model.Column;
import ro.prjmanager.database.model.Table;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import static ro.prjmanager.constants.BackendConstants.*;

/**
 * This class read all tables and all relations between them
 * from the specified schema                      
 */
public class DataBaseReader {
   
    String dbDriver;
    String dbUrl;
    String dbUser;
    String dbPwd;
    String schema;
    Connection con;

    private Log log= LogFactory.getLog(ro.prjmanager.database.DataBaseReader.class);

    /**
     * A factory method that return the unique instance
     * of this class
     * @return - The unique instance of this class
     */
    public synchronized static ro.prjmanager.database.DataBaseReader getInstance(){
        return new DataBaseReader();
    }

    public synchronized static ro.prjmanager.database.DataBaseReader getInstance(String dbDriver,String dbUrl,
                                                                                 String dbUser, String dbPwd, String schema){
        return new DataBaseReader(dbDriver,dbUrl, dbUser, dbPwd, schema);
    }



    public DataBaseReader(String dbDriver,String dbUrl, String dbUser, String dbPwd, String schema) {
        this.dbUrl = dbUrl;
        this.dbUser = dbUser;
        this.dbPwd = dbPwd;
        this.schema = schema;
        try{
            Class.forName(dbDriver).newInstance();
            con= DriverManager.getConnection(dbUrl,dbUser,dbPwd);
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    /**
     * This is the default constructor
     * In the constructor there are read the database.config file and these properties
     * are injected to the class members
     */
    private DataBaseReader(){
        BundleClReader bm= BundleClReader.getInstance(DATABASE_CONFIG_PATH+"/"+DATABASE_CONFIG_FILE);
        this.dbDriver=bm.getString(DATABASE_DRIVER_PROPERTIES);
        this.dbUrl=bm.getString(DATABASE_URL_PROPERTIES);
        this.dbUser=bm.getString(DATABASE_USER_PROPERTIES);
        this.dbPwd=bm.getString(DATABASE_PWD_PROPERTIES);
        this.schema=bm.getString(DATABASE_SCHEMA_PROPERTIES);
         try{
            Class.forName(dbDriver).newInstance();
            con= DriverManager.getConnection(dbUrl,dbUser,dbPwd);
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    public Connection getCon() {
        return con;
    }

    /**
     * Thsi method read the primary key of an specified file and inject out an Column object
     * @param tableName - The table name
     * @return - an Column object populated with the primary key column properties
     */
    private Column getPrimaryKey(String tableName){
        Column col=new Column();
        try {
            ResultSet primaryKeySet=con.getMetaData().getPrimaryKeys(con.getCatalog(),schema.toUpperCase(),tableName);
            while(primaryKeySet.next()){
                col.setPrimaryKey(true);
                String colDbaName=primaryKeySet.getString(IMP_FK_COLUMN_FK_NAME);
                String colJavaName= Utils.dataBaseNameToJavaNameAttributeStyle(colDbaName);
                col.setDatabaseColumnName(colDbaName);
                col.setJavaColumnName(colJavaName);
                String tableJavaName=Utils.dataBaseNameToJavaNameClassStyle(tableName);
                Table table=new Table();
                table.setDatabaseName(tableName);
                table.setJavaName(tableJavaName);
                col.setOwnerTable(table);
            }
        } catch (SQLException e) {
            log.error(e);
        }
        return col;
    }

    /**
     * This method extract all foreign key of the specified table name
     * and encapsulate them into an list of column
     * @param tableName - The specifed table name
     * @return - List<Column> all foreign key of the specified table
     */
    private List<Column> getForeignKeys(String tableName){
        ResultSet rs;
        List<Column> ret = null;
        try {
            rs = con.getMetaData().getImportedKeys(con.getCatalog(),schema.toUpperCase(),tableName.toUpperCase());
            ret=new ArrayList<Column>();
            while(rs.next()){
                Column col=new Column();
                Column colRef=new Column();
                String colName=rs.getString(IMP_FK_COLUMN_NAME);
                col.setDatabaseColumnName(colName);
                col.setJavaColumnName(Utils.dataBaseNameToJavaNameAttributeStyle(colName));
                Table ownerTable=new Table();
                ownerTable.setDatabaseName(tableName);
                ownerTable.setJavaName(Utils.dataBaseNameToJavaNameClassStyle(tableName));
                col.setOwnerTable(ownerTable);
                col.setForeignKey(true);
                String foreignColumnName=rs.getString(IMP_FK_COLUMN_FK_NAME);
                String tableRef=rs.getString(IMP_FK_TABLE_REF_NAMe);
                colRef.setDatabaseColumnName(foreignColumnName);
                colRef.setJavaColumnName(Utils.dataBaseNameToJavaNameAttributeStyle(foreignColumnName));
                Table refTable=new Table();
                refTable.setDatabaseName(tableRef);
                refTable.setJavaName(Utils.dataBaseNameToJavaNameClassStyle(tableRef));
                colRef.setOwnerTable(refTable);
                col.setForeingColumn(colRef);
                ret.add(col);
            }
            rs.close();
        } catch (SQLException e) {
            log.error(e);
        }
        return ret;

    }

    /**
     * This method finds and returns all columns from other tables where the column specified
     * of this table is refernced by an foreign key
     * @param tableName - The table name
     * @param column - The column for that are finded relations
     */
    private void bindColumnRelations(String tableName,Column column){
        ResultSet rs;
        try {
            rs = con.getMetaData().getExportedKeys(con.getCatalog(),schema.toUpperCase(),tableName.toUpperCase());
            while(rs.next()){
                String columnName=rs.getString(EXP_FK_COLUMN_NAME);
                String columnRef=rs.getString(EXP_FK_COLUMN_REF_NAME);
                String tableRefStr=rs.getString(EXP_FK_REF_TABLE_NAME);
                if(column.getDatabaseColumnName().equals(columnName)){
                    Column refCol=new Column();
                    refCol.setDatabaseColumnName(columnRef);
                    refCol.setJavaColumnName(Utils.dataBaseNameToJavaNameAttributeStyle(columnRef)+Utils.dataBaseNameToJavaNameClassStyle(tableRefStr));
                    Table tableRef=new Table(tableRefStr, Utils.dataBaseNameToJavaNameClassStyle(tableRefStr));
                    refCol.setOwnerTable(tableRef);
                    column.add(refCol);
                }
            }
            rs.close();
        } catch (SQLException e) {
            log.error(e);
        }
    }

    /**
     * This method maps an database table to an java Table object
     * @param tableName - String the table name
     * @return - The mapped table object
     */
    private Table getTable(String tableName){
       ResultSet rs;
       Table ret=null;
        try {
            ret=new Table();
            ret.setDatabaseName(tableName);
            ret.setJavaName(Utils.dataBaseNameToJavaNameClassStyle(tableName));
            rs = con.getMetaData().getColumns(null,null,tableName.toUpperCase(), ALL_COLUMNS_SEARCH);

            while(rs.next()){
                Column col=new Column();
                String coldbName=rs.getString(COLUMN_NAME);
                col.setDatabaseColumnName(coldbName);
                col.setJavaColumnName(Utils.dataBaseNameToJavaNameAttributeStyle(coldbName));
                col.setIdx(rs.getInt(ORDINAL_POSITION));
                col.setColumnSize(rs.getInt(COLUMN_SIZE));
                String coldbType=rs.getString(TYPE_NAME);
                col.setDatabaseType(coldbType);
                col.setJavaType(Type.fromDatabaseToJava(coldbType));
                col.setNullable((rs.getInt(NULLABLE) == IS_NULL));
                col.setOwnerTable(ret.clone());
                ret.add(col);
            }
        }catch (SQLException e){
            log.error(e);
        }
        return ret;
    }

    /**
     * This method merge the primary key column into the table
     * @param table - Table
     * @param primaryKeyColumn -Column primary key column
     */
    private void mergePrimaryKeyToTable(Table table,Column primaryKeyColumn,boolean unidirectional){
        if(table==null||primaryKeyColumn==null)
            return;
        java.util.Date  data1=new java.util.Date() ;
        Column col=table.getColumn(primaryKeyColumn.getDatabaseColumnName());
        System.out.println("Table: "+table.getDatabaseName());
        java.util.Date  data2=new java.util.Date() ;
        System.out.println("getPKColumn..."+(data2.getTime()-data1.getTime()));
        try{
        col.merge(primaryKeyColumn);
        }catch (Exception e){
            throw new NullPointerException("Nu exista primary key pt tabela: "+table.getDatabaseName());
        }
        java.util.Date  data3=new java.util.Date() ;
        System.out.println("mergePk..."+(data3.getTime()-data2.getTime()));
        if(!unidirectional)
            bindColumnRelations(table.getDatabaseName(), col);
        java.util.Date  data4=new java.util.Date() ;
        System.out.println("bindColumnRelations..."+(data4.getTime()-data3.getTime()));
    }

    /**
     * This method merge all foreign key column into the specified table
     * @param table - Table
     * @param foreignColumns - The foreign key of the specified table
     * @param primaryKeyColumn - The primaryKeyColumn
     */
    private void mergeForeignKey(Table table,List<Column> foreignColumns,Column primaryKeyColumn){

        if(table==null||foreignColumns==null||foreignColumns.isEmpty())
            return;
        for(Column fCol:foreignColumns){
            Column col=table.getColumn(fCol.getDatabaseColumnName());
            Column primaryKeyForForeignKey=getPrimaryKey(fCol.getOwnerTable().getDatabaseName());
            if(primaryKeyForForeignKey.getDatabaseColumnName().equals(col.getDatabaseColumnName())){
                table.setExtend(true);
                table.setDatabasePk(primaryKeyColumn.getDatabaseColumnName());
                table.setClassExtend(fCol.getForeingColumn().getOwnerTable().getJavaName());
                primaryKeyColumn.setForeignKey(true);
                primaryKeyColumn.setForeingColumn(col);
            }
                col.merge(fCol);

        }


    }

    /**
     * This method load an table from databse with all relations
     * @param tableName - The specified table name
     * @param unidirectional - true if there is an unidirectional generation code betwenn this entity and others
     * @return - An fully definition of an database table
     */
    public Table loadTable(String tableName,boolean unidirectional){

        Table table=getTable(tableName);


        List<Column> foColumns = getForeignKeys(tableName);


        Column pCol=getPrimaryKey(tableName);


        mergePrimaryKeyToTable(table,pCol,unidirectional);


        mergeForeignKey(table,foColumns,pCol);

    
        Column colPk=table.getColumn(pCol.getDatabaseColumnName());


        table.setPkClass(colPk.getJavaType());


        table.setUnidirectionalAssociation(unidirectional);
        return table;
    }



    public static void main(String[] args) {
        ro.prjmanager.database.DataBaseReader dba=getInstance();

//        System.out.println(dba.loadTable("CM_USER"));
       Table table=dba.loadTable("CM_USERS",true);
        System.out.println(table);

    }
    public String toString() {
        return "DataBaseReader{" +
                "dbDriver='" + dbDriver + '\'' +
                ", dbUrl='" + dbUrl + '\'' +
                ", dbUser='" + dbUser + '\'' +
                ", dbPwd='" + dbPwd + '\'' +
                '}';
    }

    public String getDbDriver() {
        return dbDriver;
    }

    public void setDbDriver(String dbDriver) {
        this.dbDriver = dbDriver;
    }

    public String getDbUrl() {
        return dbUrl;
    }

    public void setDbUrl(String dbUrl) {
        this.dbUrl = dbUrl;
    }

    public String getDbUser() {
        return dbUser;
    }

    public void setDbUser(String dbUser) {
        this.dbUser = dbUser;
    }

    public String getDbPwd() {
        return dbPwd;
    }

    public void setDbPwd(String dbPwd) {
        this.dbPwd = dbPwd;
    }

    public String getSchema() {
        return schema;
    }

    public void setSchema(String schema) {
        this.schema = schema;
    }

    public void releaseConnection() throws SQLException {
        if(con!=null){
            con.close();
        }
    }
}